{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "218fdf33",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MySQL 连接关闭\n"
     ]
    },
    {
     "ename": "XLRDError",
     "evalue": "Unsupported format, or corrupt file: Expected BOF record; found b'\\rAdminis'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mXLRDError\u001b[0m                                 Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[1], line 30\u001b[0m\n\u001b[0;32m     27\u001b[0m excel_path \u001b[38;5;241m=\u001b[39m os\u001b[38;5;241m.\u001b[39mpath\u001b[38;5;241m.\u001b[39mjoin(excel_folder, file_name)\n\u001b[0;32m     29\u001b[0m \u001b[38;5;66;03m# 读取 Excel 文件，从第9行开始读取数据\u001b[39;00m\n\u001b[1;32m---> 30\u001b[0m df \u001b[38;5;241m=\u001b[39m \u001b[43mpd\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mread_excel\u001b[49m\u001b[43m(\u001b[49m\u001b[43mexcel_path\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mskiprows\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;241;43m8\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mengine\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mxlrd\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m)\u001b[49m  \u001b[38;5;66;03m# 或 engine='xlrd'\u001b[39;00m\n\u001b[0;32m     32\u001b[0m \u001b[38;5;66;03m# 将数据插入数据库\u001b[39;00m\n\u001b[0;32m     33\u001b[0m df\u001b[38;5;241m.\u001b[39mto_sql(target_table, connection, if_exists\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mappend\u001b[39m\u001b[38;5;124m'\u001b[39m, index\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mFalse\u001b[39;00m)\n",
      "File \u001b[1;32m~\\AppData\\Roaming\\Python\\Python311\\site-packages\\pandas\\io\\excel\\_base.py:504\u001b[0m, in \u001b[0;36mread_excel\u001b[1;34m(io, sheet_name, header, names, index_col, usecols, dtype, engine, converters, true_values, false_values, skiprows, nrows, na_values, keep_default_na, na_filter, verbose, parse_dates, date_parser, date_format, thousands, decimal, comment, skipfooter, storage_options, dtype_backend, engine_kwargs)\u001b[0m\n\u001b[0;32m    502\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(io, ExcelFile):\n\u001b[0;32m    503\u001b[0m     should_close \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;01mTrue\u001b[39;00m\n\u001b[1;32m--> 504\u001b[0m     io \u001b[38;5;241m=\u001b[39m \u001b[43mExcelFile\u001b[49m\u001b[43m(\u001b[49m\n\u001b[0;32m    505\u001b[0m \u001b[43m        \u001b[49m\u001b[43mio\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    506\u001b[0m \u001b[43m        \u001b[49m\u001b[43mstorage_options\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mstorage_options\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    507\u001b[0m \u001b[43m        \u001b[49m\u001b[43mengine\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mengine\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    508\u001b[0m \u001b[43m        \u001b[49m\u001b[43mengine_kwargs\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mengine_kwargs\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    509\u001b[0m \u001b[43m    \u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    510\u001b[0m \u001b[38;5;28;01melif\u001b[39;00m engine \u001b[38;5;129;01mand\u001b[39;00m engine \u001b[38;5;241m!=\u001b[39m io\u001b[38;5;241m.\u001b[39mengine:\n\u001b[0;32m    511\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mValueError\u001b[39;00m(\n\u001b[0;32m    512\u001b[0m         \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mEngine should not be specified when passing \u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[0;32m    513\u001b[0m         \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124man ExcelFile - ExcelFile already has the engine set\u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[0;32m    514\u001b[0m     )\n",
      "File \u001b[1;32m~\\AppData\\Roaming\\Python\\Python311\\site-packages\\pandas\\io\\excel\\_base.py:1580\u001b[0m, in \u001b[0;36mExcelFile.__init__\u001b[1;34m(self, path_or_buffer, engine, storage_options, engine_kwargs)\u001b[0m\n\u001b[0;32m   1577\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mengine \u001b[38;5;241m=\u001b[39m engine\n\u001b[0;32m   1578\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mstorage_options \u001b[38;5;241m=\u001b[39m storage_options\n\u001b[1;32m-> 1580\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_reader \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_engines\u001b[49m\u001b[43m[\u001b[49m\u001b[43mengine\u001b[49m\u001b[43m]\u001b[49m\u001b[43m(\u001b[49m\n\u001b[0;32m   1581\u001b[0m \u001b[43m    \u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_io\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   1582\u001b[0m \u001b[43m    \u001b[49m\u001b[43mstorage_options\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mstorage_options\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   1583\u001b[0m \u001b[43m    \u001b[49m\u001b[43mengine_kwargs\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mengine_kwargs\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   1584\u001b[0m \u001b[43m\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[1;32m~\\AppData\\Roaming\\Python\\Python311\\site-packages\\pandas\\io\\excel\\_xlrd.py:45\u001b[0m, in \u001b[0;36mXlrdReader.__init__\u001b[1;34m(self, filepath_or_buffer, storage_options, engine_kwargs)\u001b[0m\n\u001b[0;32m     43\u001b[0m err_msg \u001b[38;5;241m=\u001b[39m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mInstall xlrd >= 2.0.1 for xls Excel support\u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[0;32m     44\u001b[0m import_optional_dependency(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mxlrd\u001b[39m\u001b[38;5;124m\"\u001b[39m, extra\u001b[38;5;241m=\u001b[39merr_msg)\n\u001b[1;32m---> 45\u001b[0m \u001b[38;5;28;43msuper\u001b[39;49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[38;5;21;43m__init__\u001b[39;49m\u001b[43m(\u001b[49m\n\u001b[0;32m     46\u001b[0m \u001b[43m    \u001b[49m\u001b[43mfilepath_or_buffer\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m     47\u001b[0m \u001b[43m    \u001b[49m\u001b[43mstorage_options\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mstorage_options\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m     48\u001b[0m \u001b[43m    \u001b[49m\u001b[43mengine_kwargs\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mengine_kwargs\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m     49\u001b[0m \u001b[43m\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[1;32m~\\AppData\\Roaming\\Python\\Python311\\site-packages\\pandas\\io\\excel\\_base.py:582\u001b[0m, in \u001b[0;36mBaseExcelReader.__init__\u001b[1;34m(self, filepath_or_buffer, storage_options, engine_kwargs)\u001b[0m\n\u001b[0;32m    580\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mhandles\u001b[38;5;241m.\u001b[39mhandle\u001b[38;5;241m.\u001b[39mseek(\u001b[38;5;241m0\u001b[39m)\n\u001b[0;32m    581\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[1;32m--> 582\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mbook \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mload_workbook\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mhandles\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mhandle\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mengine_kwargs\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    583\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mException\u001b[39;00m:\n\u001b[0;32m    584\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mclose()\n",
      "File \u001b[1;32m~\\AppData\\Roaming\\Python\\Python311\\site-packages\\pandas\\io\\excel\\_xlrd.py:62\u001b[0m, in \u001b[0;36mXlrdReader.load_workbook\u001b[1;34m(self, filepath_or_buffer, engine_kwargs)\u001b[0m\n\u001b[0;32m     60\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mhasattr\u001b[39m(filepath_or_buffer, \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mread\u001b[39m\u001b[38;5;124m\"\u001b[39m):\n\u001b[0;32m     61\u001b[0m     data \u001b[38;5;241m=\u001b[39m filepath_or_buffer\u001b[38;5;241m.\u001b[39mread()\n\u001b[1;32m---> 62\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43mopen_workbook\u001b[49m\u001b[43m(\u001b[49m\u001b[43mfile_contents\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mdata\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43mengine_kwargs\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m     63\u001b[0m \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[0;32m     64\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m open_workbook(filepath_or_buffer, \u001b[38;5;241m*\u001b[39m\u001b[38;5;241m*\u001b[39mengine_kwargs)\n",
      "File \u001b[1;32m~\\AppData\\Roaming\\Python\\Python311\\site-packages\\xlrd\\__init__.py:172\u001b[0m, in \u001b[0;36mopen_workbook\u001b[1;34m(filename, logfile, verbosity, use_mmap, file_contents, encoding_override, formatting_info, on_demand, ragged_rows, ignore_workbook_corruption)\u001b[0m\n\u001b[0;32m    169\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m file_format \u001b[38;5;129;01mand\u001b[39;00m file_format \u001b[38;5;241m!=\u001b[39m \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mxls\u001b[39m\u001b[38;5;124m'\u001b[39m:\n\u001b[0;32m    170\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m XLRDError(FILE_FORMAT_DESCRIPTIONS[file_format]\u001b[38;5;241m+\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m; not supported\u001b[39m\u001b[38;5;124m'\u001b[39m)\n\u001b[1;32m--> 172\u001b[0m bk \u001b[38;5;241m=\u001b[39m \u001b[43mopen_workbook_xls\u001b[49m\u001b[43m(\u001b[49m\n\u001b[0;32m    173\u001b[0m \u001b[43m    \u001b[49m\u001b[43mfilename\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mfilename\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    174\u001b[0m \u001b[43m    \u001b[49m\u001b[43mlogfile\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mlogfile\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    175\u001b[0m \u001b[43m    \u001b[49m\u001b[43mverbosity\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mverbosity\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    176\u001b[0m \u001b[43m    \u001b[49m\u001b[43muse_mmap\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43muse_mmap\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    177\u001b[0m \u001b[43m    \u001b[49m\u001b[43mfile_contents\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mfile_contents\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    178\u001b[0m \u001b[43m    \u001b[49m\u001b[43mencoding_override\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mencoding_override\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    179\u001b[0m \u001b[43m    \u001b[49m\u001b[43mformatting_info\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mformatting_info\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    180\u001b[0m \u001b[43m    \u001b[49m\u001b[43mon_demand\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mon_demand\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    181\u001b[0m \u001b[43m    \u001b[49m\u001b[43mragged_rows\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mragged_rows\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    182\u001b[0m \u001b[43m    \u001b[49m\u001b[43mignore_workbook_corruption\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mignore_workbook_corruption\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    183\u001b[0m \u001b[43m\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    185\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m bk\n",
      "File \u001b[1;32m~\\AppData\\Roaming\\Python\\Python311\\site-packages\\xlrd\\book.py:79\u001b[0m, in \u001b[0;36mopen_workbook_xls\u001b[1;34m(filename, logfile, verbosity, use_mmap, file_contents, encoding_override, formatting_info, on_demand, ragged_rows, ignore_workbook_corruption)\u001b[0m\n\u001b[0;32m     77\u001b[0m t1 \u001b[38;5;241m=\u001b[39m perf_counter()\n\u001b[0;32m     78\u001b[0m bk\u001b[38;5;241m.\u001b[39mload_time_stage_1 \u001b[38;5;241m=\u001b[39m t1 \u001b[38;5;241m-\u001b[39m t0\n\u001b[1;32m---> 79\u001b[0m biff_version \u001b[38;5;241m=\u001b[39m \u001b[43mbk\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mgetbof\u001b[49m\u001b[43m(\u001b[49m\u001b[43mXL_WORKBOOK_GLOBALS\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m     80\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m biff_version:\n\u001b[0;32m     81\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m XLRDError(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mCan\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mt determine file\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124ms BIFF version\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n",
      "File \u001b[1;32m~\\AppData\\Roaming\\Python\\Python311\\site-packages\\xlrd\\book.py:1284\u001b[0m, in \u001b[0;36mBook.getbof\u001b[1;34m(self, rqd_stream)\u001b[0m\n\u001b[0;32m   1282\u001b[0m     bof_error(\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mExpected BOF record; met end of file\u001b[39m\u001b[38;5;124m'\u001b[39m)\n\u001b[0;32m   1283\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m opcode \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;129;01min\u001b[39;00m bofcodes:\n\u001b[1;32m-> 1284\u001b[0m     \u001b[43mbof_error\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mExpected BOF record; found \u001b[39;49m\u001b[38;5;132;43;01m%r\u001b[39;49;00m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m \u001b[49m\u001b[38;5;241;43m%\u001b[39;49m\u001b[43m \u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mmem\u001b[49m\u001b[43m[\u001b[49m\u001b[43msavpos\u001b[49m\u001b[43m:\u001b[49m\u001b[43msavpos\u001b[49m\u001b[38;5;241;43m+\u001b[39;49m\u001b[38;5;241;43m8\u001b[39;49m\u001b[43m]\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m   1285\u001b[0m length \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mget2bytes()\n\u001b[0;32m   1286\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m length \u001b[38;5;241m==\u001b[39m MY_EOF:\n",
      "File \u001b[1;32m~\\AppData\\Roaming\\Python\\Python311\\site-packages\\xlrd\\book.py:1278\u001b[0m, in \u001b[0;36mBook.getbof.<locals>.bof_error\u001b[1;34m(msg)\u001b[0m\n\u001b[0;32m   1277\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21mbof_error\u001b[39m(msg):\n\u001b[1;32m-> 1278\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m XLRDError(\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mUnsupported format, or corrupt file: \u001b[39m\u001b[38;5;124m'\u001b[39m \u001b[38;5;241m+\u001b[39m msg)\n",
      "\u001b[1;31mXLRDError\u001b[0m: Unsupported format, or corrupt file: Expected BOF record; found b'\\rAdminis'"
     ]
    }
   ],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import mysql.connector\n",
    "\n",
    "# 配置数据库连接信息\n",
    "connection_config = {\n",
    "    'host': '127.0.0.1',        # MySQL 服务器地址\n",
    "    'user': 'root',              # MySQL 用户名\n",
    "    'password': '081450zyc',     # MySQL 密码\n",
    "    'database': '88888'          # MySQL 数据库名称\n",
    "}\n",
    "\n",
    "# 文件夹路径\n",
    "excel_folder = r'D:\\日报\\拜耳店\\sku数据'\n",
    "\n",
    "# 数据库表名\n",
    "target_table = '商品sku数据'\n",
    "\n",
    "# 连接到 MySQL 数据库\n",
    "try:\n",
    "    connection = mysql.connector.connect(**connection_config)\n",
    "    cursor = connection.cursor()\n",
    "\n",
    "    # 循环处理每个 Excel 文件\n",
    "    for file_name in os.listdir(excel_folder):\n",
    "        if file_name.endswith('.xlsx'):\n",
    "            excel_path = os.path.join(excel_folder, file_name)\n",
    "\n",
    "            # 读取 Excel 文件，从第9行开始读取数据\n",
    "            df = pd.read_excel(excel_path, skiprows=8, engine='xlrd')  # 或 engine='xlrd'\n",
    "\n",
    "            # 将数据插入数据库\n",
    "            df.to_sql(target_table, connection, if_exists='append', index=False)\n",
    "\n",
    "    print(\"数据导入成功\")\n",
    "\n",
    "except mysql.connector.Error as err:\n",
    "    print(f\"MySQL 错误: {err}\")\n",
    "\n",
    "finally:\n",
    "    # 关闭数据库连接\n",
    "    if 'connection' in locals() and connection.is_connected():\n",
    "        cursor.close()\n",
    "        connection.close()\n",
    "        print(\"MySQL 连接关闭\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "3ec2ca10",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Defaulting to user installation because normal site-packages is not writeable\n",
      "Requirement already satisfied: pandas in d:\\anaconda\\lib\\site-packages (2.0.3)\n",
      "Collecting pandas\n",
      "  Obtaining dependency information for pandas from https://files.pythonhosted.org/packages/11/17/fb1a34f3e73debbc2fd15a01ea17eaab3717943d08463ff4979a4f024b3f/pandas-2.1.4-cp311-cp311-win_amd64.whl.metadata\n",
      "  Downloading pandas-2.1.4-cp311-cp311-win_amd64.whl.metadata (18 kB)\n",
      "Requirement already satisfied: openpyxl in d:\\anaconda\\lib\\site-packages (3.0.10)\n",
      "Collecting openpyxl\n",
      "  Downloading openpyxl-3.1.2-py2.py3-none-any.whl (249 kB)\n",
      "     ---------------------------------------- 0.0/250.0 kB ? eta -:--:--\n",
      "     ---- ---------------------------------- 30.7/250.0 kB 1.4 MB/s eta 0:00:01\n",
      "     ---- ---------------------------------- 30.7/250.0 kB 1.4 MB/s eta 0:00:01\n",
      "     ------ ------------------------------ 41.0/250.0 kB 326.8 kB/s eta 0:00:01\n",
      "     ---------------- ------------------- 112.6/250.0 kB 595.3 kB/s eta 0:00:01\n",
      "     -------------------- --------------- 143.4/250.0 kB 568.9 kB/s eta 0:00:01\n",
      "     ------------------------------------ 250.0/250.0 kB 904.7 kB/s eta 0:00:00\n",
      "Requirement already satisfied: numpy<2,>=1.23.2 in d:\\anaconda\\lib\\site-packages (from pandas) (1.24.3)\n",
      "Requirement already satisfied: python-dateutil>=2.8.2 in d:\\anaconda\\lib\\site-packages (from pandas) (2.8.2)\n",
      "Requirement already satisfied: pytz>=2020.1 in d:\\anaconda\\lib\\site-packages (from pandas) (2023.3.post1)\n",
      "Requirement already satisfied: tzdata>=2022.1 in d:\\anaconda\\lib\\site-packages (from pandas) (2023.3)\n",
      "Requirement already satisfied: et-xmlfile in d:\\anaconda\\lib\\site-packages (from openpyxl) (1.1.0)\n",
      "Requirement already satisfied: six>=1.5 in d:\\anaconda\\lib\\site-packages (from python-dateutil>=2.8.2->pandas) (1.16.0)\n",
      "Downloading pandas-2.1.4-cp311-cp311-win_amd64.whl (10.6 MB)\n",
      "   ---------------------------------------- 0.0/10.6 MB ? eta -:--:--\n",
      "   ---------------------------------------- 0.0/10.6 MB 991.0 kB/s eta 0:00:11\n",
      "   ---------------------------------------- 0.1/10.6 MB 1.3 MB/s eta 0:00:08\n",
      "    --------------------------------------- 0.1/10.6 MB 1.2 MB/s eta 0:00:09\n",
      "    --------------------------------------- 0.2/10.6 MB 1.0 MB/s eta 0:00:11\n",
      "    --------------------------------------- 0.2/10.6 MB 1.0 MB/s eta 0:00:11\n",
      "    --------------------------------------- 0.2/10.6 MB 655.4 kB/s eta 0:00:16\n",
      "    --------------------------------------- 0.3/10.6 MB 787.7 kB/s eta 0:00:14\n",
      "   - -------------------------------------- 0.3/10.6 MB 776.2 kB/s eta 0:00:14\n",
      "   - -------------------------------------- 0.4/10.6 MB 892.7 kB/s eta 0:00:12\n",
      "   - -------------------------------------- 0.5/10.6 MB 1.0 MB/s eta 0:00:11\n",
      "   -- ------------------------------------- 0.5/10.6 MB 1.0 MB/s eta 0:00:10\n",
      "   -- ------------------------------------- 0.6/10.6 MB 1.0 MB/s eta 0:00:10\n",
      "   -- ------------------------------------- 0.6/10.6 MB 1.1 MB/s eta 0:00:10\n",
      "   -- ------------------------------------- 0.7/10.6 MB 1.1 MB/s eta 0:00:10\n",
      "   -- ------------------------------------- 0.8/10.6 MB 1.1 MB/s eta 0:00:09\n",
      "   -- ------------------------------------- 0.8/10.6 MB 1.1 MB/s eta 0:00:09\n",
      "   --- ------------------------------------ 0.9/10.6 MB 1.1 MB/s eta 0:00:09\n",
      "   --- ------------------------------------ 1.0/10.6 MB 1.2 MB/s eta 0:00:09\n",
      "   --- ------------------------------------ 1.0/10.6 MB 1.1 MB/s eta 0:00:09\n",
      "   ---- ----------------------------------- 1.1/10.6 MB 1.2 MB/s eta 0:00:08\n",
      "   ---- ----------------------------------- 1.1/10.6 MB 1.2 MB/s eta 0:00:08\n",
      "   ---- ----------------------------------- 1.2/10.6 MB 1.2 MB/s eta 0:00:09\n",
      "   ---- ----------------------------------- 1.2/10.6 MB 1.2 MB/s eta 0:00:09\n",
      "   ---- ----------------------------------- 1.3/10.6 MB 1.1 MB/s eta 0:00:09\n",
      "   ----- ---------------------------------- 1.3/10.6 MB 1.2 MB/s eta 0:00:09\n",
      "   ----- ---------------------------------- 1.4/10.6 MB 1.2 MB/s eta 0:00:08\n",
      "   ----- ---------------------------------- 1.5/10.6 MB 1.2 MB/s eta 0:00:08\n",
      "   ----- ---------------------------------- 1.5/10.6 MB 1.2 MB/s eta 0:00:08\n",
      "   ----- ---------------------------------- 1.6/10.6 MB 1.2 MB/s eta 0:00:08\n",
      "   ----- ---------------------------------- 1.6/10.6 MB 1.2 MB/s eta 0:00:08\n",
      "   ------ --------------------------------- 1.7/10.6 MB 1.2 MB/s eta 0:00:08\n",
      "   ------ --------------------------------- 1.7/10.6 MB 1.2 MB/s eta 0:00:08\n",
      "   ------ --------------------------------- 1.7/10.6 MB 1.2 MB/s eta 0:00:08\n",
      "   ------ --------------------------------- 1.8/10.6 MB 1.1 MB/s eta 0:00:08\n",
      "   ------- -------------------------------- 1.9/10.6 MB 1.2 MB/s eta 0:00:08\n",
      "   ------- -------------------------------- 1.9/10.6 MB 1.2 MB/s eta 0:00:08\n",
      "   ------- -------------------------------- 2.0/10.6 MB 1.2 MB/s eta 0:00:08\n",
      "   ------- -------------------------------- 2.0/10.6 MB 1.1 MB/s eta 0:00:08\n",
      "   ------- -------------------------------- 2.0/10.6 MB 1.1 MB/s eta 0:00:08\n",
      "   ------- -------------------------------- 2.0/10.6 MB 1.1 MB/s eta 0:00:08\n",
      "   -------- ------------------------------- 2.2/10.6 MB 1.1 MB/s eta 0:00:08\n",
      "   -------- ------------------------------- 2.2/10.6 MB 1.1 MB/s eta 0:00:08\n",
      "   -------- ------------------------------- 2.3/10.6 MB 1.2 MB/s eta 0:00:08\n",
      "   --------- ------------------------------ 2.6/10.6 MB 1.3 MB/s eta 0:00:07\n",
      "   --------- ------------------------------ 2.7/10.6 MB 1.3 MB/s eta 0:00:07\n",
      "   ---------- ----------------------------- 2.7/10.6 MB 1.3 MB/s eta 0:00:07\n",
      "   ---------- ----------------------------- 2.8/10.6 MB 1.3 MB/s eta 0:00:06\n",
      "   ----------- ---------------------------- 3.0/10.6 MB 1.3 MB/s eta 0:00:06\n",
      "   ----------- ---------------------------- 3.0/10.6 MB 1.3 MB/s eta 0:00:06\n",
      "   ----------- ---------------------------- 3.0/10.6 MB 1.3 MB/s eta 0:00:06\n",
      "   ------------ --------------------------- 3.3/10.6 MB 1.4 MB/s eta 0:00:06\n",
      "   ------------ --------------------------- 3.3/10.6 MB 1.4 MB/s eta 0:00:06\n",
      "   ------------ --------------------------- 3.3/10.6 MB 1.4 MB/s eta 0:00:06\n",
      "   ------------ --------------------------- 3.4/10.6 MB 1.4 MB/s eta 0:00:06\n",
      "   ------------- -------------------------- 3.5/10.6 MB 1.4 MB/s eta 0:00:06\n",
      "   ------------- -------------------------- 3.6/10.6 MB 1.4 MB/s eta 0:00:06\n",
      "   ------------- -------------------------- 3.6/10.6 MB 1.4 MB/s eta 0:00:06\n",
      "   ------------- -------------------------- 3.7/10.6 MB 1.4 MB/s eta 0:00:06\n",
      "   -------------- ------------------------- 3.8/10.6 MB 1.4 MB/s eta 0:00:05\n",
      "   -------------- ------------------------- 3.8/10.6 MB 1.4 MB/s eta 0:00:05\n",
      "   -------------- ------------------------- 3.8/10.6 MB 1.4 MB/s eta 0:00:05\n",
      "   -------------- ------------------------- 3.9/10.6 MB 1.3 MB/s eta 0:00:06\n",
      "   -------------- ------------------------- 4.0/10.6 MB 1.4 MB/s eta 0:00:05\n",
      "   --------------- ------------------------ 4.0/10.6 MB 1.3 MB/s eta 0:00:05\n",
      "   --------------- ------------------------ 4.0/10.6 MB 1.3 MB/s eta 0:00:05\n",
      "   --------------- ------------------------ 4.2/10.6 MB 1.4 MB/s eta 0:00:05\n",
      "   ---------------- ----------------------- 4.4/10.6 MB 1.4 MB/s eta 0:00:05\n",
      "   ---------------- ----------------------- 4.4/10.6 MB 1.4 MB/s eta 0:00:05\n",
      "   ---------------- ----------------------- 4.5/10.6 MB 1.4 MB/s eta 0:00:05\n",
      "   ---------------- ----------------------- 4.5/10.6 MB 1.4 MB/s eta 0:00:05\n",
      "   ---------------- ----------------------- 4.5/10.6 MB 1.4 MB/s eta 0:00:05\n",
      "   ----------------- ---------------------- 4.6/10.6 MB 1.4 MB/s eta 0:00:05\n",
      "   ----------------- ---------------------- 4.7/10.6 MB 1.4 MB/s eta 0:00:05\n",
      "   ----------------- ---------------------- 4.7/10.6 MB 1.4 MB/s eta 0:00:05\n",
      "   ------------------ --------------------- 4.9/10.6 MB 1.4 MB/s eta 0:00:04\n",
      "   ------------------- -------------------- 5.1/10.6 MB 1.4 MB/s eta 0:00:04\n",
      "   ------------------- -------------------- 5.1/10.6 MB 1.4 MB/s eta 0:00:04\n",
      "   -------------------- ------------------- 5.5/10.6 MB 1.5 MB/s eta 0:00:04\n",
      "   -------------------- ------------------- 5.5/10.6 MB 1.5 MB/s eta 0:00:04\n",
      "   --------------------- ------------------ 5.6/10.6 MB 1.5 MB/s eta 0:00:04\n",
      "   --------------------- ------------------ 5.7/10.6 MB 1.5 MB/s eta 0:00:04\n",
      "   --------------------- ------------------ 5.7/10.6 MB 1.5 MB/s eta 0:00:04\n",
      "   ---------------------- ----------------- 5.9/10.6 MB 1.5 MB/s eta 0:00:04\n",
      "   ---------------------- ----------------- 6.1/10.6 MB 1.6 MB/s eta 0:00:03\n",
      "   ---------------------- ----------------- 6.1/10.6 MB 1.6 MB/s eta 0:00:03\n",
      "   ----------------------- ---------------- 6.3/10.6 MB 1.6 MB/s eta 0:00:03\n",
      "   ------------------------ --------------- 6.5/10.6 MB 1.6 MB/s eta 0:00:03\n",
      "   ------------------------ --------------- 6.5/10.6 MB 1.6 MB/s eta 0:00:03\n",
      "   ------------------------ --------------- 6.6/10.6 MB 1.6 MB/s eta 0:00:03\n",
      "   ------------------------- -------------- 6.7/10.6 MB 1.6 MB/s eta 0:00:03\n",
      "   ------------------------- -------------- 6.9/10.6 MB 1.6 MB/s eta 0:00:03\n",
      "   -------------------------- ------------- 7.0/10.6 MB 1.6 MB/s eta 0:00:03\n",
      "   -------------------------- ------------- 7.0/10.6 MB 1.6 MB/s eta 0:00:03\n",
      "   -------------------------- ------------- 7.1/10.6 MB 1.6 MB/s eta 0:00:03\n",
      "   -------------------------- ------------- 7.2/10.6 MB 1.6 MB/s eta 0:00:03\n",
      "   --------------------------- ------------ 7.2/10.6 MB 1.6 MB/s eta 0:00:03\n",
      "   --------------------------- ------------ 7.3/10.6 MB 1.6 MB/s eta 0:00:03\n",
      "   --------------------------- ------------ 7.4/10.6 MB 1.6 MB/s eta 0:00:03\n",
      "   ---------------------------- ----------- 7.5/10.6 MB 1.6 MB/s eta 0:00:02\n",
      "   ---------------------------- ----------- 7.6/10.6 MB 1.6 MB/s eta 0:00:02\n",
      "   ---------------------------- ----------- 7.6/10.6 MB 1.6 MB/s eta 0:00:02\n",
      "   ---------------------------- ----------- 7.6/10.6 MB 1.6 MB/s eta 0:00:02\n",
      "   ---------------------------- ----------- 7.7/10.6 MB 1.6 MB/s eta 0:00:02\n",
      "   ----------------------------- ---------- 7.8/10.6 MB 1.6 MB/s eta 0:00:02\n",
      "   ----------------------------- ---------- 7.9/10.6 MB 1.6 MB/s eta 0:00:02\n",
      "   ----------------------------- ---------- 7.9/10.6 MB 1.6 MB/s eta 0:00:02\n",
      "   ------------------------------ --------- 8.0/10.6 MB 1.6 MB/s eta 0:00:02\n",
      "   ------------------------------ --------- 8.1/10.6 MB 1.6 MB/s eta 0:00:02\n",
      "   ------------------------------- -------- 8.3/10.6 MB 1.6 MB/s eta 0:00:02\n",
      "   ------------------------------- -------- 8.3/10.6 MB 1.6 MB/s eta 0:00:02\n",
      "   ------------------------------- -------- 8.4/10.6 MB 1.6 MB/s eta 0:00:02\n",
      "   -------------------------------- ------- 8.5/10.6 MB 1.6 MB/s eta 0:00:02\n",
      "   -------------------------------- ------- 8.6/10.6 MB 1.6 MB/s eta 0:00:02\n",
      "   -------------------------------- ------- 8.6/10.6 MB 1.6 MB/s eta 0:00:02\n",
      "   --------------------------------- ------ 8.8/10.6 MB 1.6 MB/s eta 0:00:02\n",
      "   --------------------------------- ------ 8.9/10.6 MB 1.7 MB/s eta 0:00:02\n",
      "   ---------------------------------- ----- 9.1/10.6 MB 1.7 MB/s eta 0:00:01\n",
      "   ---------------------------------- ----- 9.1/10.6 MB 1.7 MB/s eta 0:00:01\n",
      "   ---------------------------------- ----- 9.2/10.6 MB 1.7 MB/s eta 0:00:01\n",
      "   ---------------------------------- ----- 9.2/10.6 MB 1.7 MB/s eta 0:00:01\n",
      "   ----------------------------------- ---- 9.3/10.6 MB 1.7 MB/s eta 0:00:01\n",
      "   ----------------------------------- ---- 9.4/10.6 MB 1.7 MB/s eta 0:00:01\n",
      "   ----------------------------------- ---- 9.4/10.6 MB 1.7 MB/s eta 0:00:01\n",
      "   ------------------------------------ --- 9.6/10.6 MB 1.7 MB/s eta 0:00:01\n",
      "   ------------------------------------ --- 9.7/10.6 MB 1.7 MB/s eta 0:00:01\n",
      "   ------------------------------------ --- 9.7/10.6 MB 1.7 MB/s eta 0:00:01\n",
      "   ------------------------------------ --- 9.7/10.6 MB 1.7 MB/s eta 0:00:01\n",
      "   ------------------------------------- -- 9.9/10.6 MB 1.7 MB/s eta 0:00:01\n",
      "   ------------------------------------- -- 10.0/10.6 MB 1.7 MB/s eta 0:00:01\n",
      "   ------------------------------------- -- 10.0/10.6 MB 1.7 MB/s eta 0:00:01\n",
      "   ------------------------------------- -- 10.0/10.6 MB 1.7 MB/s eta 0:00:01\n",
      "   -------------------------------------- - 10.1/10.6 MB 1.7 MB/s eta 0:00:01\n",
      "   -------------------------------------- - 10.3/10.6 MB 1.7 MB/s eta 0:00:01\n",
      "   ---------------------------------------  10.4/10.6 MB 1.7 MB/s eta 0:00:01\n",
      "   ---------------------------------------  10.4/10.6 MB 1.7 MB/s eta 0:00:01\n",
      "   ---------------------------------------  10.5/10.6 MB 1.7 MB/s eta 0:00:01\n",
      "   ---------------------------------------  10.6/10.6 MB 1.7 MB/s eta 0:00:01\n",
      "   ---------------------------------------- 10.6/10.6 MB 1.7 MB/s eta 0:00:00\n",
      "Installing collected packages: openpyxl, pandas\n",
      "Successfully installed openpyxl-3.1.2 pandas-2.1.4\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "pip install --upgrade pandas openpyxl\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "937f1448",
   "metadata": {},
   "outputs": [
    {
     "ename": "BadZipFile",
     "evalue": "File is not a zip file",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mBadZipFile\u001b[0m                                Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[3], line 28\u001b[0m\n\u001b[0;32m     25\u001b[0m \u001b[38;5;28;01mfor\u001b[39;00m file \u001b[38;5;129;01min\u001b[39;00m file_list:\n\u001b[0;32m     26\u001b[0m     \u001b[38;5;66;03m# 5. 检查文件类型并读取数据\u001b[39;00m\n\u001b[0;32m     27\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m file\u001b[38;5;241m.\u001b[39mendswith(\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m.xlsx\u001b[39m\u001b[38;5;124m'\u001b[39m):  \u001b[38;5;66;03m# 如果是 Excel 文件\u001b[39;00m\n\u001b[1;32m---> 28\u001b[0m         xls \u001b[38;5;241m=\u001b[39m \u001b[43mpd\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mExcelFile\u001b[49m\u001b[43m(\u001b[49m\u001b[43mos\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mpath\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mjoin\u001b[49m\u001b[43m(\u001b[49m\u001b[43mfolder_path\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mfile\u001b[49m\u001b[43m)\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mengine\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mopenpyxl\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m)\u001b[49m\n\u001b[0;32m     29\u001b[0m         df \u001b[38;5;241m=\u001b[39m pd\u001b[38;5;241m.\u001b[39mread_excel(xls, sheet_name\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mSheet1\u001b[39m\u001b[38;5;124m'\u001b[39m, skiprows\u001b[38;5;241m=\u001b[39m\u001b[38;5;241m8\u001b[39m)\n\u001b[0;32m     30\u001b[0m     \u001b[38;5;28;01melif\u001b[39;00m file\u001b[38;5;241m.\u001b[39mendswith(\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m.csv\u001b[39m\u001b[38;5;124m'\u001b[39m):  \u001b[38;5;66;03m# 如果是 CSV 文件\u001b[39;00m\n",
      "File \u001b[1;32m~\\AppData\\Roaming\\Python\\Python311\\site-packages\\pandas\\io\\excel\\_base.py:1580\u001b[0m, in \u001b[0;36mExcelFile.__init__\u001b[1;34m(self, path_or_buffer, engine, storage_options, engine_kwargs)\u001b[0m\n\u001b[0;32m   1577\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mengine \u001b[38;5;241m=\u001b[39m engine\n\u001b[0;32m   1578\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mstorage_options \u001b[38;5;241m=\u001b[39m storage_options\n\u001b[1;32m-> 1580\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_reader \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_engines\u001b[49m\u001b[43m[\u001b[49m\u001b[43mengine\u001b[49m\u001b[43m]\u001b[49m\u001b[43m(\u001b[49m\n\u001b[0;32m   1581\u001b[0m \u001b[43m    \u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_io\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   1582\u001b[0m \u001b[43m    \u001b[49m\u001b[43mstorage_options\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mstorage_options\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   1583\u001b[0m \u001b[43m    \u001b[49m\u001b[43mengine_kwargs\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mengine_kwargs\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   1584\u001b[0m \u001b[43m\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[1;32m~\\AppData\\Roaming\\Python\\Python311\\site-packages\\pandas\\io\\excel\\_openpyxl.py:553\u001b[0m, in \u001b[0;36mOpenpyxlReader.__init__\u001b[1;34m(self, filepath_or_buffer, storage_options, engine_kwargs)\u001b[0m\n\u001b[0;32m    541\u001b[0m \u001b[38;5;250m\u001b[39m\u001b[38;5;124;03m\"\"\"\u001b[39;00m\n\u001b[0;32m    542\u001b[0m \u001b[38;5;124;03mReader using openpyxl engine.\u001b[39;00m\n\u001b[0;32m    543\u001b[0m \n\u001b[1;32m   (...)\u001b[0m\n\u001b[0;32m    550\u001b[0m \u001b[38;5;124;03m    Arbitrary keyword arguments passed to excel engine.\u001b[39;00m\n\u001b[0;32m    551\u001b[0m \u001b[38;5;124;03m\"\"\"\u001b[39;00m\n\u001b[0;32m    552\u001b[0m import_optional_dependency(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mopenpyxl\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n\u001b[1;32m--> 553\u001b[0m \u001b[38;5;28;43msuper\u001b[39;49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[38;5;21;43m__init__\u001b[39;49m\u001b[43m(\u001b[49m\n\u001b[0;32m    554\u001b[0m \u001b[43m    \u001b[49m\u001b[43mfilepath_or_buffer\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    555\u001b[0m \u001b[43m    \u001b[49m\u001b[43mstorage_options\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mstorage_options\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    556\u001b[0m \u001b[43m    \u001b[49m\u001b[43mengine_kwargs\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mengine_kwargs\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    557\u001b[0m \u001b[43m\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[1;32m~\\AppData\\Roaming\\Python\\Python311\\site-packages\\pandas\\io\\excel\\_base.py:582\u001b[0m, in \u001b[0;36mBaseExcelReader.__init__\u001b[1;34m(self, filepath_or_buffer, storage_options, engine_kwargs)\u001b[0m\n\u001b[0;32m    580\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mhandles\u001b[38;5;241m.\u001b[39mhandle\u001b[38;5;241m.\u001b[39mseek(\u001b[38;5;241m0\u001b[39m)\n\u001b[0;32m    581\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[1;32m--> 582\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mbook \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mload_workbook\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mhandles\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mhandle\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mengine_kwargs\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    583\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mException\u001b[39;00m:\n\u001b[0;32m    584\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mclose()\n",
      "File \u001b[1;32m~\\AppData\\Roaming\\Python\\Python311\\site-packages\\pandas\\io\\excel\\_openpyxl.py:570\u001b[0m, in \u001b[0;36mOpenpyxlReader.load_workbook\u001b[1;34m(self, filepath_or_buffer, engine_kwargs)\u001b[0m\n\u001b[0;32m    565\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21mload_workbook\u001b[39m(\n\u001b[0;32m    566\u001b[0m     \u001b[38;5;28mself\u001b[39m, filepath_or_buffer: FilePath \u001b[38;5;241m|\u001b[39m ReadBuffer[\u001b[38;5;28mbytes\u001b[39m], engine_kwargs\n\u001b[0;32m    567\u001b[0m ) \u001b[38;5;241m-\u001b[39m\u001b[38;5;241m>\u001b[39m Workbook:\n\u001b[0;32m    568\u001b[0m     \u001b[38;5;28;01mfrom\u001b[39;00m \u001b[38;5;21;01mopenpyxl\u001b[39;00m \u001b[38;5;28;01mimport\u001b[39;00m load_workbook\n\u001b[1;32m--> 570\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43mload_workbook\u001b[49m\u001b[43m(\u001b[49m\n\u001b[0;32m    571\u001b[0m \u001b[43m        \u001b[49m\u001b[43mfilepath_or_buffer\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    572\u001b[0m \u001b[43m        \u001b[49m\u001b[43mread_only\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;28;43;01mTrue\u001b[39;49;00m\u001b[43m,\u001b[49m\n\u001b[0;32m    573\u001b[0m \u001b[43m        \u001b[49m\u001b[43mdata_only\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;28;43;01mTrue\u001b[39;49;00m\u001b[43m,\u001b[49m\n\u001b[0;32m    574\u001b[0m \u001b[43m        \u001b[49m\u001b[43mkeep_links\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;28;43;01mFalse\u001b[39;49;00m\u001b[43m,\u001b[49m\n\u001b[0;32m    575\u001b[0m \u001b[43m        \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43mengine_kwargs\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    576\u001b[0m \u001b[43m    \u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[1;32m~\\AppData\\Roaming\\Python\\Python311\\site-packages\\openpyxl\\reader\\excel.py:344\u001b[0m, in \u001b[0;36mload_workbook\u001b[1;34m(filename, read_only, keep_vba, data_only, keep_links, rich_text)\u001b[0m\n\u001b[0;32m    314\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21mload_workbook\u001b[39m(filename, read_only\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mFalse\u001b[39;00m, keep_vba\u001b[38;5;241m=\u001b[39mKEEP_VBA,\n\u001b[0;32m    315\u001b[0m                   data_only\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mFalse\u001b[39;00m, keep_links\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mTrue\u001b[39;00m, rich_text\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mFalse\u001b[39;00m):\n\u001b[0;32m    316\u001b[0m \u001b[38;5;250m    \u001b[39m\u001b[38;5;124;03m\"\"\"Open the given filename and return the workbook\u001b[39;00m\n\u001b[0;32m    317\u001b[0m \n\u001b[0;32m    318\u001b[0m \u001b[38;5;124;03m    :param filename: the path to open or a file-like object\u001b[39;00m\n\u001b[1;32m   (...)\u001b[0m\n\u001b[0;32m    342\u001b[0m \n\u001b[0;32m    343\u001b[0m \u001b[38;5;124;03m    \"\"\"\u001b[39;00m\n\u001b[1;32m--> 344\u001b[0m     reader \u001b[38;5;241m=\u001b[39m \u001b[43mExcelReader\u001b[49m\u001b[43m(\u001b[49m\u001b[43mfilename\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mread_only\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mkeep_vba\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    345\u001b[0m \u001b[43m                         \u001b[49m\u001b[43mdata_only\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mkeep_links\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mrich_text\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    346\u001b[0m     reader\u001b[38;5;241m.\u001b[39mread()\n\u001b[0;32m    347\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m reader\u001b[38;5;241m.\u001b[39mwb\n",
      "File \u001b[1;32m~\\AppData\\Roaming\\Python\\Python311\\site-packages\\openpyxl\\reader\\excel.py:123\u001b[0m, in \u001b[0;36mExcelReader.__init__\u001b[1;34m(self, fn, read_only, keep_vba, data_only, keep_links, rich_text)\u001b[0m\n\u001b[0;32m    121\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21m__init__\u001b[39m(\u001b[38;5;28mself\u001b[39m, fn, read_only\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mFalse\u001b[39;00m, keep_vba\u001b[38;5;241m=\u001b[39mKEEP_VBA,\n\u001b[0;32m    122\u001b[0m              data_only\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mFalse\u001b[39;00m, keep_links\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mTrue\u001b[39;00m, rich_text\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mFalse\u001b[39;00m):\n\u001b[1;32m--> 123\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39marchive \u001b[38;5;241m=\u001b[39m \u001b[43m_validate_archive\u001b[49m\u001b[43m(\u001b[49m\u001b[43mfn\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    124\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mvalid_files \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39marchive\u001b[38;5;241m.\u001b[39mnamelist()\n\u001b[0;32m    125\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mread_only \u001b[38;5;241m=\u001b[39m read_only\n",
      "File \u001b[1;32m~\\AppData\\Roaming\\Python\\Python311\\site-packages\\openpyxl\\reader\\excel.py:95\u001b[0m, in \u001b[0;36m_validate_archive\u001b[1;34m(filename)\u001b[0m\n\u001b[0;32m     88\u001b[0m             msg \u001b[38;5;241m=\u001b[39m (\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mopenpyxl does not support \u001b[39m\u001b[38;5;132;01m%s\u001b[39;00m\u001b[38;5;124m file format, \u001b[39m\u001b[38;5;124m'\u001b[39m\n\u001b[0;32m     89\u001b[0m                    \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mplease check you can open \u001b[39m\u001b[38;5;124m'\u001b[39m\n\u001b[0;32m     90\u001b[0m                    \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mit with Excel first. \u001b[39m\u001b[38;5;124m'\u001b[39m\n\u001b[0;32m     91\u001b[0m                    \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mSupported formats are: \u001b[39m\u001b[38;5;132;01m%s\u001b[39;00m\u001b[38;5;124m'\u001b[39m) \u001b[38;5;241m%\u001b[39m (file_format,\n\u001b[0;32m     92\u001b[0m                                                    \u001b[38;5;124m'\u001b[39m\u001b[38;5;124m,\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;241m.\u001b[39mjoin(SUPPORTED_FORMATS))\n\u001b[0;32m     93\u001b[0m         \u001b[38;5;28;01mraise\u001b[39;00m InvalidFileException(msg)\n\u001b[1;32m---> 95\u001b[0m archive \u001b[38;5;241m=\u001b[39m \u001b[43mZipFile\u001b[49m\u001b[43m(\u001b[49m\u001b[43mfilename\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mr\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m)\u001b[49m\n\u001b[0;32m     96\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m archive\n",
      "File \u001b[1;32mD:\\Anaconda\\Lib\\zipfile.py:1302\u001b[0m, in \u001b[0;36mZipFile.__init__\u001b[1;34m(self, file, mode, compression, allowZip64, compresslevel, strict_timestamps, metadata_encoding)\u001b[0m\n\u001b[0;32m   1300\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[0;32m   1301\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m mode \u001b[38;5;241m==\u001b[39m \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mr\u001b[39m\u001b[38;5;124m'\u001b[39m:\n\u001b[1;32m-> 1302\u001b[0m         \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_RealGetContents\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m   1303\u001b[0m     \u001b[38;5;28;01melif\u001b[39;00m mode \u001b[38;5;129;01min\u001b[39;00m (\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mw\u001b[39m\u001b[38;5;124m'\u001b[39m, \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mx\u001b[39m\u001b[38;5;124m'\u001b[39m):\n\u001b[0;32m   1304\u001b[0m         \u001b[38;5;66;03m# set the modified flag so central directory gets written\u001b[39;00m\n\u001b[0;32m   1305\u001b[0m         \u001b[38;5;66;03m# even if no files are added to the archive\u001b[39;00m\n\u001b[0;32m   1306\u001b[0m         \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_didModify \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;01mTrue\u001b[39;00m\n",
      "File \u001b[1;32mD:\\Anaconda\\Lib\\zipfile.py:1369\u001b[0m, in \u001b[0;36mZipFile._RealGetContents\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m   1367\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m BadZipFile(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mFile is not a zip file\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n\u001b[0;32m   1368\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m endrec:\n\u001b[1;32m-> 1369\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m BadZipFile(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mFile is not a zip file\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n\u001b[0;32m   1370\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mdebug \u001b[38;5;241m>\u001b[39m \u001b[38;5;241m1\u001b[39m:\n\u001b[0;32m   1371\u001b[0m     \u001b[38;5;28mprint\u001b[39m(endrec)\n",
      "\u001b[1;31mBadZipFile\u001b[0m: File is not a zip file"
     ]
    }
   ],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import pymysql\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "# 1. 连接到 MySQL 数据库\n",
    "# 请替换以下信息为你的数据库连接信息\n",
    "db_config = {\n",
    "    'host': '127.0.0.1',\n",
    "    'user': 'root',\n",
    "    'password': '081450zyc',\n",
    "    'database': '88888',\n",
    "    'port': 3306,\n",
    "}\n",
    "\n",
    "engine = create_engine(f\"mysql+pymysql://{db_config['user']}:{db_config['password']}@{db_config['host']}:{db_config['port']}/{db_config['database']}\")\n",
    "\n",
    "# 2. 指定文件夹路径\n",
    "folder_path = r'D:\\日报\\拜耳店\\sku数据'\n",
    "\n",
    "# 3. 获取文件夹中所有文件的列表\n",
    "file_list = os.listdir(folder_path)\n",
    "\n",
    "# 4. 循环处理每个文件\n",
    "for file in file_list:\n",
    "    # 5. 检查文件类型并读取数据\n",
    "    if file.endswith('.xlsx'):  # 如果是 Excel 文件\n",
    "        xls = pd.ExcelFile(os.path.join(folder_path, file))\n",
    "        df = pd.read_excel(xls, sheet_name='Sheet1', skiprows=8)\n",
    "    elif file.endswith('.csv'):  # 如果是 CSV 文件\n",
    "        df = pd.read_csv(os.path.join(folder_path, file), skiprows=8)  # 从第9行开始读取数据\n",
    "\n",
    "    # 6. 将数据插入数据库表中\n",
    "    df.to_sql('your_table_name', engine, if_exists='append', index=False)\n",
    "\n",
    "# 7. 关闭数据库连接\n",
    "engine.dispose()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "57059225",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Defaulting to user installation because normal site-packages is not writeable\n",
      "Requirement already satisfied: pandas in c:\\users\\pc\\appdata\\roaming\\python\\python311\\site-packages (2.1.4)\n",
      "Requirement already satisfied: numpy<2,>=1.23.2 in d:\\anaconda\\lib\\site-packages (from pandas) (1.24.3)\n",
      "Requirement already satisfied: python-dateutil>=2.8.2 in d:\\anaconda\\lib\\site-packages (from pandas) (2.8.2)\n",
      "Requirement already satisfied: pytz>=2020.1 in d:\\anaconda\\lib\\site-packages (from pandas) (2023.3.post1)\n",
      "Requirement already satisfied: tzdata>=2022.1 in d:\\anaconda\\lib\\site-packages (from pandas) (2023.3)\n",
      "Requirement already satisfied: six>=1.5 in d:\\anaconda\\lib\\site-packages (from python-dateutil>=2.8.2->pandas) (1.16.0)\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "pip install --upgrade pandas\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "7ce12c36",
   "metadata": {},
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "invalid syntax (2261780373.py, line 1)",
     "output_type": "error",
     "traceback": [
      "\u001b[1;36m  Cell \u001b[1;32mIn[7], line 1\u001b[1;36m\u001b[0m\n\u001b[1;33m    pythonCopy codeimport xlrd\u001b[0m\n\u001b[1;37m               ^\u001b[0m\n\u001b[1;31mSyntaxError\u001b[0m\u001b[1;31m:\u001b[0m invalid syntax\n"
     ]
    }
   ],
   "source": [
    "pythonCopy codeimport xlrd\n",
    "print(xlrd.open_workbook(\"your_file.xlsx\").format)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "37afd864",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
